package com.css.fxfzxczh.modules.samplingPointDataCensus.emergencystatistics.repository.impl;

import com.css.fxfzxczh.modules.disasterSituationDataManage.emergencyReport.entity.EmergencyReport;
import com.css.fxfzxczh.modules.samplingPointDataCensus.emergencystatistics.entity.EmergencyPoint;
import com.css.fxfzxczh.modules.samplingPointDataCensus.emergencystatistics.entity.EmergencyProportion;
import com.css.fxfzxczh.modules.samplingPointDataCensus.emergencystatistics.repository.EmergencyReportStatisticsRepository;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.*;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/02/21
 * @Description: 灾情急报统计
 */
@Repository
public class EmergencyReportStatisticsRepositoryImpl implements EmergencyReportStatisticsRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "xczh_disaster_emergency_report"; // 表名
    private static final String TABLE_ID = "xder_id"; // 表id字段名


    //伤亡、埋压/被困
    @Override
    public EmergencyProportion getCasualtyStatistically(String xsemId) {
        String sql="select sum(death) as casualty ,sum(people_trapped) as peopleTrapped from " +
                TABLE_NAME +
                " where xsem_id = '"+xsemId+"' and del_flag='0'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EmergencyProportion.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //房屋倒塌
    @Override
    public List<String> getHouseCollapse(String xsemId) {
        //大量
        String sql1="select COUNT(*) as value from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and building_collapse ='大量'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //少量
        String sql2="select COUNT(*) as value from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and building_collapse ='少量'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //个别
        String sql3="select COUNT(*) as value from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and building_collapse ='个别'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    //崩塌
    @Override
    public List<String> getCollapse(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and crumble ='大规模'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and crumble ='中等规模'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and crumble ='小规模'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    //滑坡
    @Override
    public List<String> getLandslide(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and landslip ='大规模'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and landslip ='中等规模'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and landslip ='小规模'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    //道路拥堵/中断
    @Override
    public List<String> getRoad(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and congestion_disruption ='完全中断'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and congestion_disruption ='严重拥堵'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and congestion_disruption ='交通不畅'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    //火灾
    @Override
    public List<String> getFire(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and fire ='大规模'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and fire ='中等规模'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and fire ='小规模'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    @Override
    public List<Map<String,Object>> getStatisticalResult(String xsemId,String type) {
        String sql="select scale as name,count(scale) as value from xczh_disaster_emergency_report where xsem_id= ? and disaster_type=? group by scale";
        List<Map<String, Object>> map = jdbcTemplate.queryForList(sql,xsemId,type);
        return map;
    }

    @Override
    public List<EmergencyReport> queryAll(String type, String xsemId) {
        StringBuilder sql=new StringBuilder("select *,st_astext(geom) geom from xczh_disaster_emergency_report where del_flag='0' and xsem_id='"+xsemId+"' and ( ");
        if(PlatformObjectUtils.isNotEmpty(type)){
            List<String> list = Arrays.asList(type.split(","));
            for (int i = 0; i < list.size(); i++) {
                if(i==0){
                    sql.append(" type like '%"+list.get(i)+"%'");
                }else{
                    sql.append(" or type like '%"+list.get(i)+"%'");
                }
            }
        }

        sql.append(")");
        List<EmergencyReport> emergencyPointList = namedParameterJdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EmergencyReport.class));
        return emergencyPointList;
    }

    @Override
    public List<String> getOther(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and type like '%9%' and hazardous_material_leakage ='大规模'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and type like '%9%' and hazardous_material_leakage ='中等规模'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and type like '%9%' and hazardous_material_leakage ='小规模'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }

    @Override
    public List<String> getDangerousGoods(String xsemId) {
        //大规模
        String sql1="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and hazardous_material_leakage ='大规模'";
        String s1 = jdbcTemplate.queryForObject(sql1, String.class);
        //中等规模
        String sql2="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and hazardous_material_leakage ='中等规模'";
        String s2 = jdbcTemplate.queryForObject(sql2, String.class);
        //小规模
        String sql3="select COUNT(*) as value  from "+TABLE_NAME+" where xsem_id = '"+xsemId+"' and del_flag='0' and hazardous_material_leakage ='小规模'";
        String s3 = jdbcTemplate.queryForObject(sql3, String.class);
        List<String> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        list.add(s3);
        return list;
    }
}
